﻿using System;
using System.Configuration;
using System.Data;
using System.Data.Common;

namespace UltraDBFactoring
{
    /// <summary>
    /// Class responsible for Helping the access to the data information.
    /// </summary>
    public class DataAccessHelper : IDisposable
    {
        /// <summary>
        /// Private member for Database factoring.
        /// </summary>
        private DbProviderFactory cnnFactory = null;

        /// <summary>
        /// Connection object.
        /// </summary>
        private DbConnection cnn = null;

        /// <summary>
        /// The class constructor.
        /// </summary>
        /// <param name="openConnection">If true then opens the connection.</param>
        public DataAccessHelper(bool openConnection)
        {
            if (openConnection)
            {
                this.OpenConnection();
            }
        }

        /// <summary>
        /// Retrieves the connection that must be performed.
        /// </summary>
        private string ActiveEnvironment
        {
            get
            {
                return ConfigurationManager.AppSettings["ACTIVE_ENVIRONMENT"].ToString();
            }
        }

        /// <summary>
        /// Opens the connection to the database.
        /// </summary>
        public void OpenConnection()
        {
            if (this.cnn == null)
            {
                try
                {
                    this.cnnFactory = ConnectionFactoryDB.CreateDataBaseObject(this.ActiveEnvironment);
                    this.cnn = this.cnnFactory.CreateConnection();
                    this.cnn.ConnectionString = ConnectionFactoryDB.GetConnectionStringByName(this.ActiveEnvironment).ConnectionString;
                    this.cnn.Open();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        /// <summary>
        /// Closes the connection to the database.
        /// </summary>
        public void CloseConnection()
        {
            try
            {
                if (this.cnn != null)
                {
                    if (this.cnn.State == ConnectionState.Open)
                    {
                        this.cnn.Close();
                        this.cnn.Dispose();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Method to fetch the data.
        /// </summary>
        /// <param name="sql">The command in SQL to query the data.</param>
        /// <returns>Returns a DataTable.</returns>
        public DataTable Fetch(string sql)
        {
            try
            {
                using (DbCommand sc = this.cnnFactory.CreateCommand())
                {
                    using (DbDataAdapter da = this.cnnFactory.CreateDataAdapter())
                    {
                        using (DataTable dt = new DataTable())
                        {
                            sc.CommandText = sql;
                            sc.Connection = this.cnn;
                            da.SelectCommand = sc;
                            da.Fill(dt);

                            return dt;
                        }
                    }
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Method for executing instructions like insert, update and delete data.
        /// </summary>
        /// <param name="sql">The command to run SQL statements insert, update, delete data.</param>
        /// <returns>Number of rows affected.</returns>
        public int Execute(string sql)
        {
            int count = 0;

            try
            {
                using (DbCommand ic = this.cnnFactory.CreateCommand())
                {
                    ic.CommandText = sql;
                    ic.Connection = this.cnn;
                    count = ic.ExecuteNonQuery();
                }

                return count;
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Verifica se a conexão está aberta.
        /// </summary>
        public bool IsConnectionOpened
        {
            get
            {
                if (this.cnn != null)
                {
                    return this.cnn.State == ConnectionState.Open;
                }
                else
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// Closes the connection when the Object is Disposed.
        /// </summary>
        public void Dispose()
        {
            this.CloseConnection();
        }
    }
}
